package com.ruoyi.project.qyxy.statistics.mapper;

import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface StatisticsMapper {

    /**
     * 获取资质统计信息
     * @return
     */
    @Select("select aa.city,aa.cnt,bb.cnt1,cc.cnt2 from (\n" +
            "select b.CITY,count(*) as cnt from QYXY_ORG_SCORE a,T_ENTERPRISE_INFO b\n" +
            "\n" +
            "where a.ORG_CODE = b.ORG_CODE\n" +
            "and b.PROVINCE = '黑龙江'\n" +
            " \n" +
            "group by b.CITY\n" +
            "order by count(*) desc\n" +
            ") aa,(select b.CITY,count(*) as cnt1 from QYXY_ORG_SCORE a,T_ENTERPRISE_INFO b,QYXY_ORG_SCORE_DETAIL c\n" +
            "where a.ORG_CODE = b.ORG_CODE\n" +
            "and b.org_code = c.org_code\n" +
            "and b.PROVINCE = '黑龙江'\n" +
            "and c.score>=4.2\n" +
            "and c.type = '2'\n" +
            "group by b.CITY\n" +
            ") bb,(\n" +
            "select b.CITY,count(*) as cnt2 from QYXY_ORG_SCORE a,T_ENTERPRISE_INFO b\n" +
            "where a.ORG_CODE = b.ORG_CODE\n" +
            "and b.PROVINCE = '黑龙江'\n" +
            "and a.org_code not in (select distinct a.org_code from T_PERSON_INFO a,T_REG_INFO b\n" +
            "where a.id_num =b.id_Num)\n" +
            "group by b.CITY\n" +
            "\n" +
            ") cc\n" +
            "where aa.city = bb.city(+)\n" +
            "and aa.city = cc.city(+)\n" +
            "union \n" +
            "select '本省',sum(cnt),sum(cnt1),sum(cnt2) from (select aa.city,aa.cnt,bb.cnt1,cc.cnt2 from (\n" +
            "select b.CITY,count(*) as cnt from QYXY_ORG_SCORE a,T_ENTERPRISE_INFO b\n" +
            "\n" +
            "where a.ORG_CODE = b.ORG_CODE\n" +
            "and b.PROVINCE = '黑龙江'\n" +
            " \n" +
            "group by b.CITY\n" +
            "order by count(*) desc\n" +
            ") aa,(select b.CITY,count(*) as cnt1 from QYXY_ORG_SCORE a,T_ENTERPRISE_INFO b,QYXY_ORG_SCORE_DETAIL c\n" +
            "where a.ORG_CODE = b.ORG_CODE\n" +
            "and b.org_code = c.org_code\n" +
            "and b.PROVINCE = '黑龙江'\n" +
            "and c.score>=4.2\n" +
            "and c.type = '2'\n" +
            "group by b.CITY\n" +
            ") bb,(\n" +
            "select b.CITY,count(*) as cnt2 from QYXY_ORG_SCORE a,T_ENTERPRISE_INFO b\n" +
            "where a.ORG_CODE = b.ORG_CODE\n" +
            "and b.PROVINCE = '黑龙江'\n" +
            "and a.org_code not in (select distinct a.org_code from T_PERSON_INFO a,T_REG_INFO b\n" +
            "where a.id_num =b.id_Num)\n" +
            "group by b.CITY\n" +
            "\n" +
            ") cc\n" +
            "where aa.city = bb.city(+)\n" +
            "and aa.city = cc.city(+)\n" +
            ")\n" +
            "order by cnt desc")
    List<Map> getZzStatistics();

    /**
     * 得分企业统计
     * @return
     */
    @Select("select FLOOR(score) as score,count(*) as count from (\n" +
            "select a.org_code,sum(score) score from QYXY_ORG_SCORE_DETAIL a,T_ENTERPRISE_INFO b\n" +
            "where a.ORG_CODE = b.ORG_CODE\n" +
            "and b.PROVINCE = '黑龙江'\n" +
            " group by a.org_code\n" +
            ")\n" +
            "group by FLOOR(score) order by FLOOR(score)")
    List<Map> getOrgScoreSts();
}
